

create proc sp_finished_goods_report

as

begin
	declare @Lot_ID int
	declare @ProcessStep_ID int
	declare @Parent_Product_ID int

	declare @Lot_Number varchar(50)
	declare @Base_Product varchar(50)
	declare @Part_Number varchar(50)
	declare @Process_Type varchar(50)
	declare @Location varchar(50)
	declare @Qty_In int
	declare @Qty_Issued int
	declare @Qty_Shipped int
	declare @Qty_Balance int
	declare @Date_In datetime
	declare @Lot_Status varchar(50)


	declare @results Table(Base_product varchar(50), Part_Number varchar(50) , Lot_Number varchar(50), Process_Type varchar(50), Location varchar(50), Date_In datetime ,
			       Qty_In int, Qty_Issued int, Qty_Shipped int, Qty_Balance int, Lot_Status varchar(50) )

	declare fgLotCursor cursor for
	select LT.Lot_ID, LT.ProcessStep_ID, Lot_Number, Part_Number, PS_Type_Name, L_Vendor_Name, IL_Qty_In, IL_Qty_Balance, IL_Date_In , Lot_Current_Status
	from Lot_Table LT, Inventory_Lot IL, Process_Step PS , Location , Product
	where Product.ProcessStep_ID = LT.ProcessStep_ID and LT.ProcessStep_ID = PS.ProcessStep_ID and LT.Lot_ID = IL.Lot_ID and Location.Location_ID = LT.Location_ID and PS_Type_Name = 'Finished Goods'


	open fgLotCursor
	fetch next from fgLotCursor into @Lot_ID, @ProcessStep_ID, @Lot_Number, @Part_Number, @Process_Type, @Location, @Qty_In, @Qty_Balance, @Date_In, @Lot_Status 

	while (@@fetch_status = 0)
	begin
		if ( @Lot_Status = 'Running' )
		begin
			set @Lot_Status = 'ACTIVE'
		
			select @Parent_Product_ID = P_Parent_Product_ID FROM Product WHERE ProcessStep_ID = @ProcessStep_ID
			if( ( @Parent_Product_ID is null ) or ( @Parent_Product_ID = -1 ) )
			begin	
				select @Base_Product = Part_Number from Product where ProcessStep_ID = @ProcessStep_ID
			end
			else		
			begin
				select @Base_Product = Part_Number from Product where Product_Id = ( Select P_Parent_Product_Id from Product where ProcessStep_ID = @ProcessStep_ID )	
			end

			if ( @Qty_Balance is null )
			begin
				set @Qty_Balance = @Qty_In
			end

			select @Qty_Issued = isNull( sum(CSLNT_Qty_Pull),0 ) from Customer_Shipping_Lot_Number_Table where CSLNT_Status is null and Lot_ID = @Lot_ID
			select @Qty_Shipped = isNull( sum(CSLNT_Qty_Pull),0 ) from Customer_Shipping_Lot_Number_Table where CSLNT_Status = 'SHIPPED' and Lot_ID = @Lot_ID

			insert into @results(Base_product, Part_Number, Lot_Number, Process_Type, Location, Date_In, Qty_In, Qty_Issued, Qty_Shipped, Qty_Balance, Lot_Status )
			values (@Base_Product, @Part_Number, @Lot_Number, @Process_Type, @Location, @Date_In, @Qty_In, @Qty_Issued, @Qty_Shipped, @Qty_Balance, @Lot_Status  )
		end
		
		set @Lot_ID = null
		set @ProcessStep_ID = null
		set @Lot_Number = null
		set @Base_Product = null
		set @Part_Number = null
		set @Process_Type = null
		set @Location = null
		set @Qty_In = null
		set @Qty_Issued = null
		set @Qty_Shipped = null
		set @Qty_Balance = null
		set @Date_In = null
		set @Lot_Status  = null

		fetch next from fgLotCursor into @Lot_ID, @ProcessStep_ID, @Lot_Number, @Part_Number, @Process_Type, @Location, @Qty_In, @Qty_Balance, @Date_In, @Lot_Status 		
	end

	close fgLotCursor
	deallocate fgLotCursor
	
	select * from @results

end